package com.qf.mapper;

import com.qf.vo.DayCountStatis;
import com.qf.vo.DayStatis;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @author wanghaotian
 * time:2022/12/16,0016 23:08
 * description:
 */

public interface StatMapper {

    @Select("SELECT c1.name name , sum(og.price) value FROM dts_category c1, dts_category c2, dts_goods g, dts_order_goods og WHERE c2.pid = c1.id and g.category_id = c2.id and g.id = og.goods_id GROUP BY c1.`name`")
    List<Map<String, Object>> findCategorySellData();

    @Select("select DATE_FORMAT( add_time,\"%Y-%m-%d\") dayStr, sum(order_price) amts, count(1) cnts\n" +
            "        from dts_order\n" +
            "         where add_time >=  DATE_FORMAT(DATE_SUB(SYSDATE(),INTERVAL 1 MONTH),\"%Y-%m-%d 00:00:00\")\n" +
            "        group by DATE_FORMAT( add_time,\"%Y-%m-%d\")")
    List<DayStatis> findOrderAmts();

    @Select("select DATE_FORMAT( add_time,\"%Y-%m-%d\") dayStr, count(0) cnts from dts_user\n" +
            "        where add_time >= DATE_FORMAT(DATE_SUB(SYSDATE(),INTERVAL 1 MONTH),\"%Y-%m-%d 00:00:00\")\n" +
            "        group by DATE_FORMAT( add_time,\"%Y-%m-%d\")")
    List<DayStatis> findUserDayStatis();

    @Select("SELECT  DATE_FORMAT(add_time,'%Y-%m-%d') AS day,COUNT(1) AS users FROM dts_user " +
            "WHERE add_time > DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL #{day} DAY)  GROUP BY day;")
    List<DayCountStatis> findUserCount(int i);

    @Select("SELECT DATE_FORMAT(add_time,'%Y-%m-%d') AS dayStr,SUM(order_price) AS amts," +
            "COUNT(1) AS cnts FROM dts_order WHERE order_status NOT IN (101,102,103)" +
            " AND deleted = 0 AND add_time > DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d')," +
            "INTERVAL #{day} DAY)  GROUP BY dayStr;")
    List<DayStatis> findOrderAmt(int day);
}
